package com.neusoft.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.BusinessDao;
import com.neusoft.elm.po.Admin;
import com.neusoft.elm.po.Business;
import com.neusoft.elm.util.DBUtil;

public class BusinessDaoImpl implements BusinessDao {

	Connection con = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	
	@Override
	public List<Business> listBusiness(String businessName,String businessAddress) {
		
		//String sql = "SELECT * FROM business WHERE 1=1";(常规查询，有具体条件要求需要StringBuffer)
		StringBuffer sql = new StringBuffer("SELECT * FROM business WHERE 1=1 ");
		
		//规定商家名称不为空，或商家名称不是空的字符串
		if(businessName != null && !businessName.equals("")){
			//搜索商家名称
			sql.append("AND businessname LIKE '%" + businessName+ "%'");
		}
		
		//规定商家地址不为空，或商家地址不是空的字符串
		if(businessAddress != null && !businessAddress.equals("")){
			//搜索商家地址
			sql.append("AND businessAddress LIKE '%" + businessAddress+ "%'");
		}
		//WHERE 1=1 是为了方便后续加搜索条件，后续条件只加and
		List<Business> list = new ArrayList<Business>();
		
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			rs = pst.executeQuery();
			while(rs.next()){
				Business business = new Business();
				business.setBusinessId(rs.getInt("businessId"));
				business.setPassword(rs.getString("password"));
				business.setBusinessName(rs.getString("businessName"));
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setStarPrice(rs.getDouble("starPrice"));
				//将数据添加到集合
				list.add(business);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pst, con);
		}
		
		
		//对象形的默认返回一个null
		return list;
	}

	@Override
	public int saveBusiness(String businessName) {

		int businessId = 0;
		String sql = "INSERT INTO business (businessName,password)  VALUES (?,'123')";
		//链接数据库
		try {
			con = DBUtil.getConnection();
			//RETURN_GENERATED_KEYS:返回自动增长列的值
			pst = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			pst.setString(1,businessName);
			//java中对数据库有影响的操作，均用Update，只有查询用Query。
			pst.executeUpdate();
			//获取自动增长列，返回ResultSet（一行一列）形数据
			rs = pst.getGeneratedKeys();
			if(rs.next()){
				businessId = rs.getInt(1);//参数是取回第一列的值
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pst, con);
		}
		return businessId;
	}

	@Override
	public int removeBusiness(int businessId) {
		
		int result = 0;
		String delFoodSql = "DELETE FROM food WHERE businessId = ? ";
		String delBusinessSql = "DELETE FROM business WHERE businessId = ? ";
		
		try {
			//1.链接数据库
			con = DBUtil.getConnection();
			//2.开启事务
			con.setAutoCommit(false);
			//删除食品表格内的食品信息
			pst = con.prepareStatement(delFoodSql);
			pst.setInt(1, businessId);
			pst.executeUpdate();
			//删除商家表格内的商家信息
			pst = con.prepareStatement(delBusinessSql);
			pst.setInt(1, businessId);
			result = pst.executeUpdate();
			//事务的提交
			con.commit();
			
		} catch (SQLException e) {
			result = 0;
			try {
				//事务的回滚
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
		}finally{
			DBUtil.close(rs, pst, con);
		}
		return result;
	}

	@Override
	public Business getBusinessByIdByPass(Integer businessId, String password) {
        
		String sql = "SELECT * FROM business WHERE businessId = ? AND password = ?";
		Business business = null;
		try {
			//1.链接数据库
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			
			pst.setInt(1, businessId);
			pst.setString(2, password);
			rs = pst.executeQuery();
			
			while(rs.next()){
				business = new Business();
				business.setBusinessId(rs.getInt("businessId"));
				business.setPassword(rs.getString("password"));
				business.setBusinessName(rs.getString("businessName"));
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setStarPrice(rs.getDouble("starPrice"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pst, con);
		}
		
		return business;
	}

	@Override
	public Business showBusiness(Integer businessId) {
		String sql = "SELECT * FROM business WHERE businessId = ? ";
		Business business = null;
		try {
			//1.链接数据库
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			
			pst.setInt(1, businessId);
			rs = pst.executeQuery();
			
			while(rs.next()){
				business = new Business();
				business.setBusinessId(rs.getInt("businessId"));
				business.setPassword(rs.getString("password"));
				business.setBusinessName(rs.getString("businessName"));
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setStarPrice(rs.getDouble("starPrice"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pst, con);
		}
		return business;
	}

	@Override
	public int updateBusiness(Business business) {

		int result = 0;
		String sql = "UPDATE business SET  businessName = ?,businessAddress = ?,businessExplain = ?,starPrice = ?,deliveryPrice = ? WHERE businessId = ? ";
		//链接数据库
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			//该部分需要从属性内调用参数
			pst.setString(1, business.getBusinessName());
			pst.setString(2, business.getBusinessAddress());
			pst.setString(3, business.getBusinessExplain());
			pst.setDouble(4, business.getStarPrice());
			pst.setDouble(5, business.getDeliveryPrice());
			pst.setInt(6, business.getBusinessId());
			//java中对数据库有影响的操作，均用Update，只有查询用Query。
			result = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pst, con);
		}
		return result;
	}

	@Override
	public int updateBusinessByPassword(Integer businessId,String password) {

		int result = 0;
		String sql = "UPDATE business SET  password = ? WHERE businessId = ? ";
		//链接数据库
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, password);
			pst.setInt(2, businessId);
			//执行更新：java中对数据库有影响的操作，均用Update，只有查询用Query。
			result = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pst, con);
		}
		return result;
	}

}
